from libs import time_helper, common
from libs.db import DB


def get_inventory(cursor, filters, fields):
    dh = DB('table_inventory', conn=cursor)
    row = dh.one(fields, filters)
    return row


def count_day_inventory(cursor):
    start_time = time_helper.date_str({
        'days': 0
    })
    end_time = time_helper.date_str({
        'days': 1
    })
    db = DB('table_inventory', conn=cursor)
    return db.count('add_time>=%s and add_time<%s', [start_time, end_time]) + 1


def get_inventory_sn(cursor):
    num = count_day_inventory(cursor)
    return time_helper.ts_to_time_str(time_format='%y%m%d') + str(num).rjust(4, '0')


def get_round_sn(shop_id, ts=None):
    ym = time_helper.ts_to_time_str(ts, time_format='%y%m')
    return '{}-{}'.format(ym, str(shop_id))


def get_inventory_detail_state(val):
    inventory_detail_state = 0
    if val > 0:
        inventory_detail_state = 1
    elif val < 0:
        inventory_detail_state = 2
    return inventory_detail_state


def count_inventory_item_num(cursor, inventory_id, filters=None):
    dh = DB('table_inventory_detail', conn=cursor)
    where_map = {
        'i_id': inventory_id
    }
    if filters is not None:
        where_map.update(filters)
    return dh.count(where_map)


def get_inventory_stats_data(cursor, inventory_id, goods_total=None):
    inventory_item_num = count_inventory_item_num(cursor, inventory_id)
    plus_num = count_inventory_item_num(cursor, inventory_id, {'state': 1})
    minus_num = count_inventory_item_num(cursor, inventory_id, {'state': 2})
    no_diff_num = count_inventory_item_num(cursor, inventory_id, {'should_count=actual_count': None})
    inventory_stats_data = {
        'actual_num': inventory_item_num,
        'surplus_num': plus_num,
        'deficit_num': minus_num,
        'no_diff_num': no_diff_num,
    }
    if goods_total is not None:
        leak_num = goods_total - inventory_item_num
        inventory_stats_data.update({'leak_num': leak_num})
    return inventory_stats_data


def build_query_inventory_goods_sql(form, shop_id, flag, db_inventory):
    sto_typ = db_inventory.get('sto_typ')
    inventory_typ = db_inventory.get('type')
    inventory_add_time = db_inventory.get('add_time')
    inventory_add_ts = time_helper.time_str_to_ts(str(inventory_add_time), time_format='%Y-%m-%d %H:%M:%S')
    inventory_id = form.get('id')
    inventory_w_id = form.get('w_id')
    class_id = form.get_int('class_id')
    status = form.get_int('status', None)
    count_field = 'count(*) num'
    _sql_frag0 = ' FROM table_goods g'
    if sto_typ == 2:
        _sql_frag0 = ' FROM table_warehouse_goods g'
    _sql_frag0 += ' LEFT JOIN table_inventory_detail tid ON g.id=tid.good_id'
    _sql_frag1 = ''
    _con_flag = True
    if flag is not None:
        if flag == 0:
            _sql_frag1 = ' AND tid.id is NULL'
            _con_flag = False
        elif flag == 1:
            _sql_frag1 = ' AND tid.id>0'
        elif flag == 2:
            _sql_frag1 = ' AND tid.state=0'
        elif flag == 3:
            _sql_frag1 = ' AND tid.state=1'
        elif flag == 4:
            _sql_frag1 = ' AND tid.state=2'
    if inventory_typ == 1:
        _sql_frag0 += ' AND tid.round_sn=%s'
        round_sn = get_round_sn(shop_id, inventory_add_ts)
        bindings = [round_sn]
        if _con_flag:
            _sql_frag0 += ' AND tid.i_id=%s'
            bindings.append(inventory_id)
    else:
        _sql_frag0 += ' AND tid.i_id=%s'
        bindings = [inventory_id]
    if sto_typ == 2:
        _sql_frag0 += ' WHERE g.w_id=%s g.is_delete=0'
        bindings.append(inventory_w_id)
        query_fields = 'tid.*,g.id goods_id,g.c_id class_id,g.good_name goods_name,g.good_count scount,' \
                       'g.good_price usdt_price,g.w_id,g.product_date,g.guarantee_period,g.guarantee_type,g.good_picmain goods_img'
        if class_id > 0:
            _sql_frag0 += ' AND g.c_id=%s'
            bindings += [class_id]
    else:
        _sql_frag0 += ' WHERE g.shoper_id=%s AND g.is_delete=0'
        bindings.append(shop_id)
        query_fields = 'tid.*,g.id goods_id,g.class_id,g.goods_name,g.scount,' \
                       'g.usdt_price,g.shoper_id,g.barcode,g.product_date,g.guarantee_period,g.guarantee_type'
        if class_id > 0:
            _sql_frag0 += ' AND g.class_id=%s'
            bindings += [class_id]
    _sql_frag0 += _sql_frag1
    if status is not None:
        _sql_frag0 += ' AND tid.status=%s'
        bindings.append(status)
    sql = 'SELECT {}'.format(query_fields)
    sql += _sql_frag0
    count_sql = 'SELECT {}'.format(count_field)
    count_sql += _sql_frag0
    sql += ' ORDER BY g.id desc'
    return count_sql, sql, bindings


def count_inventory_detail_by_inventory_id(cursor, inventory_id):
    dh = DB('table_inventory_detail', conn=cursor)
    return dh.count({'i_id': inventory_id})


def stats_inventory_detail_goods_num_and_amount(cursor, inventory_id):
    dh = DB('table_inventory_detail', conn=cursor)
    sql = 'select sum(actual_count) num, sum(actual_money) amount from table_inventory_detail where i_id=%s'
    rows = dh.query(sql, [inventory_id])
    num = '0'
    amount = '0'
    for row in rows:
        num = row.get('num', '0')
        amount = row.get('amount', '0')
    if num is None:
        num = '0'
    if amount is None:
        amount = '0'
    return num, amount


def save_inventory_data(shop_id, post_data, cursor):
    row_id = 0
    err = None
    action_typ = 0  # 1增，2删，3改
    try:
        inventory_id = common.to_int(post_data.get('inventory_id', 0))
        if inventory_id == 0:
            return action_typ, row_id, '无效的盘点ID'
        goods_id = common.to_int(post_data.get('goods_id', 0))
        if goods_id == 0:
            return action_typ, row_id, '无效的商品ID'
        actual_count = common.to_dec(post_data.get('actual_count', '0'))
        flag = common.to_int(post_data.get('flag', 0))
        dh = DB('table_inventory', conn=cursor)
        inventory_fields = ['id', 'sto_typ', 'type', 'w_id']
        db_inventory = dh.one(inventory_fields, {'id': inventory_id})
        db_inventory_id = db_inventory.get('id', 0)
        if db_inventory_id == 0:
            return action_typ, row_id, '盘点单不存在'
        db_inventory_w_id = db_inventory.get('w_id')
        db_inventory_sto_typ = db_inventory.get('sto_typ')
        db_inventory_typ = db_inventory.get('type')
        dh = DB('table_inventory_detail', conn=cursor)
        inventory_detail_fields = ['id', 'good_price', 'should_count', 'should_money', 'check_status']
        db_inventory_detail = dh.one(inventory_detail_fields, {'i_id': inventory_id, 'good_id': goods_id})
        db_inventory_detail_id = db_inventory_detail.get('id', 0)
        row_id = db_inventory_detail_id
        if flag == 1:
            action_typ = 2
            if db_inventory_detail_id == 0:
                return action_typ, row_id, '盘点商品不存在，无法删除'
            affected, err = dh.delete('id=%s', [db_inventory_detail_id])
            if err is not None:
                return action_typ, row_id, err
            if affected == 0:
                return action_typ, row_id, '盘点商品删除失败'
        else:
            if db_inventory_detail_id > 0:
                action_typ = 3
                db_inventory_check_status = db_inventory_detail.get('check_status')
                if db_inventory_check_status in [1, 2]:
                    status_dict = {1: '审核中', 2: '已通过'}
                    return action_typ, row_id, '当前状态“{}”，无法修改盘点数量'.format(status_dict.get(db_inventory_check_status))
                db_inventory_should_count = db_inventory_detail.get('should_count')
                db_inventory_should_money = db_inventory_detail.get('should_money')
                db_inventory_goods_price = db_inventory_detail.get('good_price')
                actual_money = db_inventory_goods_price * actual_count
                pal_count = actual_count - db_inventory_should_count
                pal_money = actual_money - db_inventory_should_money
                state = get_inventory_detail_state(pal_count)
                data = {
                    'actual_count': actual_count,
                    'actual_money': actual_money,
                    'pal_count': pal_count,
                    'pal_money': pal_money,
                    'state': state,
                }
                if db_inventory_check_status == 3:
                    data.update({'check_status': 0})
                dh.update(data, {'id': db_inventory_detail_id})
            else:
                action_typ = 1
                if db_inventory_sto_typ == 2:
                    goods_dh = DB('table_warehouse_goods', conn=cursor)
                    goods_fields = ['id', 'cid', 'good_count', 'good_price', 'good_cost']
                    db_goods = goods_dh.one(goods_fields, 'id=%s and w_id=%s', [goods_id, db_inventory_w_id])
                    db_goods_id = db_goods.get('id', 0)
                    if db_goods_id == 0:
                        return action_typ, row_id, '仓库商品不存在'
                    class_id = db_goods.get('cid')
                    goods_price = db_goods.get('good_price')
                    cost_price = db_goods.get('good_cost')
                    should_count = db_goods.get('good_count')
                else:
                    goods_dh = DB('table_goods', conn=cursor)
                    goods_fields = ['id', 'class_id', 'scount', 'usdt_price', 'cost_price']
                    db_goods = goods_dh.one(goods_fields, 'id=%s and shoper_id=%s', [goods_id, shop_id])
                    db_goods_id = db_goods.get('id', 0)
                    if db_goods_id == 0:
                        return action_typ, row_id, '店铺商品不存在'
                    class_id = db_goods.get('class_id')
                    goods_price = common.to_dec(db_goods.get('usdt_price'))
                    cost_price = db_goods.get('cost_price')
                    should_count = db_goods.get('scount')
                should_money = should_count * goods_price
                actual_money = actual_count * goods_price
                pal_count = actual_count - should_count
                pal_money = actual_money - should_money
                state = get_inventory_detail_state(pal_count)
                if db_inventory_typ == 1:
                    round_sn = get_round_sn(shop_id)
                else:
                    round_sn = ''
                data = {
                    'i_id': inventory_id,
                    'good_id': goods_id,
                    'class_id': class_id,
                    'good_price': goods_price,
                    'good_cost': cost_price,
                    'should_count': should_count,
                    'should_money': should_money,
                    'actual_count': actual_count,
                    'actual_money': actual_money,
                    'pal_count': pal_count,
                    'pal_money': pal_money,
                    'state': state,
                    'round_sn': round_sn
                }
                row_id, err = dh.insert(data)
    except Exception as e:
        err = '保存盘点商品异常'
        print(err, e)
    return action_typ, row_id, err


# 获取一条本月盘点记录
def get_a_record_of_current_month_inventory(cursor, sto_typ, subject_id):
    mon_start_date, next_mon_start_date = time_helper.get_mon_range()
    mon_start_date_con = 'add_time>="{}"'.format(mon_start_date)
    next_mon_start_date_con = 'add_time<"{}"'.format(next_mon_start_date)
    filters = {'sto_typ': sto_typ, mon_start_date_con: None, next_mon_start_date_con: None}
    if sto_typ == 1:
        filters.update({'shop_id': subject_id})
    elif sto_typ == 2:
        filters.update({'w_id': subject_id})
    else:
        return 0
    db_inventory = get_inventory(cursor, filters, ['id'])
    return db_inventory.get('id', 0)
